package com.PMSys.Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.PMSys.Common.DbUtils;
import com.PMSys.Demain.Department;
import com.PMSys.Demain.Staff;

public class DepartmentDaoImp implements DepartmentDao {

	private Connection conn;

	public DepartmentDaoImp() {
		conn = DbUtils.getConnection();
	}

	public int add(Department dp) {

		int row = 0;
		String sql = "insert into department(d_name,d_type,d_phone,d_email,d_describe,d_superiorId,d_startDate) values(?,?,?,?,?,?,?)";
		PreparedStatement pStatement = null;
		try {
			pStatement = conn.prepareStatement(sql);

			pStatement.setString(1, dp.getD_name());
			pStatement.setInt(2, dp.getD_type());
			pStatement.setString(3, dp.getD_phone());
			pStatement.setString(4, dp.getD_email());
			pStatement.setString(5, dp.getD_describe());
			pStatement.setInt(6, dp.getD_superiorId());
			pStatement.setDate(7, new java.sql.Date(dp.getD_startDate().getTime()));

			row = pStatement.executeUpdate();
		} catch (SQLException e) {

			e.printStackTrace();
			DbUtils.rollback();
			return 0;
		}

		return row;
	}

	public int update(Department dp) {

		int row = 0;
		String sql = "update department set d_name=?,d_type=?,d_phone=?,d_email=?,d_describe=?,d_superiorId=?,d_startDate=? where d_id=?";
		PreparedStatement pStatement = null;
		try {
			pStatement = conn.prepareStatement(sql);

			pStatement.setString(1, dp.getD_name());
			pStatement.setInt(2, dp.getD_type());
			pStatement.setString(3, dp.getD_phone());
			pStatement.setString(4, dp.getD_email());
			pStatement.setString(5, dp.getD_describe());
			pStatement.setInt(6, dp.getD_superiorId());
			pStatement.setDate(7, new java.sql.Date(dp.getD_startDate().getTime()));
			pStatement.setInt(8, dp.getD_id());

			row = pStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			DbUtils.rollback();
			return 0;
		}

		return row;
	}

	public int delete(int id) {

		int row = 0;
		String sql = "delete from department where d_id=?";
		PreparedStatement pStatement = null;
		try {
			pStatement = conn.prepareStatement(sql);
			pStatement.setInt(1, id);

			row = pStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			DbUtils.rollback();
			return 0;
		}

		return row;
	}

	public Department selectById(int id) {
		String sql = "select * from department where d_id=?";
		PreparedStatement pStatement = null;
		ResultSet resultSet = null;

		Department department = null;
		try {
			pStatement = conn.prepareStatement(sql);
			pStatement.setInt(1, id);

			resultSet = pStatement.executeQuery();

			if (resultSet.next()) {
				department = getBean(resultSet);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}

		return department;
	}

	public Department selectBySuperiorId(int superiorId) {
		String sql = "select * from department where d_superiorId=?";
		PreparedStatement pStatement = null;
		ResultSet resultSet = null;

		Department department = null;
		try {
			pStatement = conn.prepareStatement(sql);
			pStatement.setInt(1, superiorId);

			resultSet = pStatement.executeQuery();

			if (resultSet.next()) {
				department = new Department();
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return department;
	}

	public List<Department> selectPart(Department dp) {

		StringBuilder sql = new StringBuilder();
		sql.append("select * from department where 1=1");

		if (dp.getD_id() != 0) {
			sql.append(" and d_id=");
			sql.append(dp.getD_id());
		}

		if (dp.getD_name() != null && !"".equals(dp.getD_name())) {
			sql.append(" and d_name like '%");
			sql.append(dp.getD_name());
			sql.append("%'");
		}

		if (dp.getD_type() != 0) {
			sql.append(" and d_type=");
			sql.append(dp.getD_type());
		}

		// System.out.println(sql.toString());
		PreparedStatement pStatement = null;
		ResultSet resultSet = null;

		List<Department> departmentList = new ArrayList<Department>();
		try {
			pStatement = conn.prepareStatement(sql.toString());

			resultSet = pStatement.executeQuery();

			while (resultSet.next()) {
				Department department = getBean(resultSet);
				departmentList.add(department);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return departmentList;
	}

	public List<Department> selectAll() {
		String sql = "select * from department";
		PreparedStatement pStatement = null;
		ResultSet resultSet = null;

		List<Department> departmentList = new ArrayList<Department>();
		try {
			pStatement = conn.prepareStatement(sql);

			resultSet = pStatement.executeQuery();

			while (resultSet.next()) {
				Department department = getBean(resultSet);
				departmentList.add(department);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return departmentList;
	}

	public List<Staff> selectDepartmentEmployees(int id) {

		Staff staff = new Staff();
		staff.setD_id("" + id);

		StaffDao staffDao = new StaffDaoImp(DbUtils.getConnection());

		return staffDao.getSelectStaffForDepartmentId(staff);
	}

	public boolean existSubordinate(int superiorId) {
		String sql = "select * from department where d_superiorId=?";
		PreparedStatement pStatement = null;
		ResultSet resultSet = null;
		try {
			pStatement = conn.prepareStatement(sql);
			pStatement.setInt(1, superiorId);
			resultSet = pStatement.executeQuery();
			if (resultSet.next()) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			DbUtils.rollback();
			return false;
		}
		return false;
	}

	public boolean existEmployee(int d_id) {
		String sql = "select * from Staff where d_id = ? and s_workform != 3 order by s_id asc";
		PreparedStatement pStatement = null;
		ResultSet resultSet = null;
		try {
			pStatement = conn.prepareStatement(sql);
			pStatement.setInt(1, d_id);
			resultSet = pStatement.executeQuery();
			if (resultSet.next()) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			DbUtils.rollback();
			return false;
		}
		return false;
	}

	private Department getBean(ResultSet resultSet) {

		Department department = null;
		try {
			if (resultSet != null) {
				department = new Department();
				department.setD_id(resultSet.getInt("d_id"));
				department.setD_name(resultSet.getString("d_name"));
				department.setD_type(resultSet.getInt("d_type"));
				department.setD_phone(resultSet.getString("d_phone"));
				department.setD_email(resultSet.getString("d_email"));
				department.setD_describe(resultSet.getString("d_describe"));
				department.setD_superiorId(resultSet.getInt("d_superiorId"));
				department.setD_startDate(resultSet.getDate("d_startDate"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}

		return department;
	}

}
